package com.hospital.dao;

import com.hospital.model.Doctor;
import com.hospital.model.Department;
import com.hospital.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DoctorDAO {
    
    public List<Doctor> findByDepartment(Integer departmentId) {
        List<Doctor> doctors = new ArrayList<>();
        String sql = "SELECT d.*, dept.name as dept_name, dept.description as dept_description " +
                    "FROM doctor d " +
                    "JOIN department dept ON d.department_id = dept.id " +
                    "WHERE d.department_id = ? AND d.status = 1";
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, departmentId);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                Doctor doctor = new Doctor();
                doctor.setId(rs.getInt("id"));
                doctor.setName(rs.getString("name"));
                doctor.setDepartmentId(rs.getInt("department_id"));
                doctor.setTitle(rs.getString("title"));
                doctor.setStatus(rs.getInt("status"));
                doctor.setCreatedAt(rs.getTimestamp("created_at"));
                doctor.setUpdatedAt(rs.getTimestamp("updated_at"));
                
                Department dept = new Department();
                dept.setId(rs.getInt("department_id"));
                dept.setName(rs.getString("dept_name"));
                dept.setDescription(rs.getString("dept_description"));
                doctor.setDepartment(dept);
                
                doctors.add(doctor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, stmt, rs);
        }
        
        return doctors;
    }
    
    public Doctor findById(Integer id) {
        Doctor doctor = null;
        String sql = "SELECT d.*, dept.name as dept_name, dept.description as dept_description " +
                    "FROM doctor d " +
                    "JOIN department dept ON d.department_id = dept.id " +
                    "WHERE d.id = ? AND d.status = 1";
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                doctor = new Doctor();
                doctor.setId(rs.getInt("id"));
                doctor.setName(rs.getString("name"));
                doctor.setDepartmentId(rs.getInt("department_id"));
                doctor.setTitle(rs.getString("title"));
                doctor.setStatus(rs.getInt("status"));
                doctor.setCreatedAt(rs.getTimestamp("created_at"));
                doctor.setUpdatedAt(rs.getTimestamp("updated_at"));
                
                Department dept = new Department();
                dept.setId(rs.getInt("department_id"));
                dept.setName(rs.getString("dept_name"));
                dept.setDescription(rs.getString("dept_description"));
                doctor.setDepartment(dept);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, stmt, rs);
        }
        
        return doctor;
    }
    
    public void updateStatus(Integer id, Integer status) {
        String sql = "UPDATE doctor SET status = ? WHERE id = ?";
        
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, status);
            stmt.setInt(2, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, stmt, null);
        }
    }
}